package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

var db *sql.DB

type CustomerInfo struct {
	ID             int
	CustomerId     int
	CustomerName   string
	CustomerPicUrl sql.NullString
	CustomerDesc   string
	CreateTime     time.Time
	ModifiedTime   time.Time
}

func initDB() (err error) {
	dsn := "root:root@tcp(127.0.0.1:3306)/blog?charset=utf8mb4&parseTime=True"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		fmt.Println(err)
		return
	}
	return
}
func queryDemo() {
	sqlStr := "select * from customer_info limit 1"
	var c CustomerInfo
	err := db.QueryRow(sqlStr).Scan(&c.ID, &c.CustomerId, &c.CustomerName, &c.CustomerPicUrl, &c.CustomerDesc, &c.CreateTime, &c.ModifiedTime)
	if err != nil {
		fmt.Printf("%v", err)
		return
	}
	fmt.Printf("%d %d %v %v", c.ID, c.CustomerId, c.CustomerName, c.CreateTime)

}
func queryMany() {
	sqlStr := "select * from customer_info where id >?"
	stmt, err := db.Prepare(sqlStr) //预处理
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	rows, err := db.Query(sqlStr, 5) //查询id>5的数据
	if err != nil {
		fmt.Println(err)
		return
	}
	for rows.Next() {
		var c CustomerInfo
		err := rows.Scan(&c.ID, &c.CustomerId, &c.CustomerName, &c.CustomerPicUrl, &c.CustomerDesc, &c.CreateTime, &c.ModifiedTime)
		if err != nil {
			fmt.Println(err)
			return
		}
		fmt.Printf("%d %d %v %v", c.ID, c.CustomerId, c.CustomerName, c.CreateTime)
		fmt.Println()
	}
}

type test struct {
	ID       int
	Name     string
	Password string
}

func insert() {
	sqlStr := "insert into test(id,name,password) values(?,?,?)"
	t := test{ID: 2, Name: "12345", Password: "12345"}
	ret, err := db.Exec(sqlStr, t.ID, t.Name, t.Password)
	if err != nil {
		fmt.Println(err)
	}
	newId, err := ret.LastInsertId() //最新插入的id
	fmt.Println(newId)

}
func updateData() {
	sqlStr := "update test set name=? where id=?"
	ret, err := db.Exec(sqlStr, "123456", 2)
	if err != nil {
		fmt.Println(err)
	}
	n, err := ret.RowsAffected() ///影响行数
	fmt.Println("影响行数", n)
}
func deleteData() {
	sqlStr := "delete from test where id=?"
	ret, err := db.Exec(sqlStr, 1)
	if err != nil {
		fmt.Println(err)
	}
	n, err := ret.RowsAffected() ///影响行数
	fmt.Println("影响行数", n)
}
func Close() {
	defer db.Close()
}
func main() {
	_ = initDB()
	//queryDemo()
	//queryMany()
	//insert()
	//updateData()
	deleteData()
	Close()

}
